﻿using System;
using System.Collections.Specialized;
using System.Collections.Generic;
using System.Data.Objects;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.EntityClient;
using System.Linq;
using System.Linq.Dynamic;
using System.Text;
using Ashlen.Portal.Core.DataInterfaces;
using Ashlen.Portal.Core;
using Ashlen.Portal.Core.DTO;
using System.Configuration;
using System.Transactions;

namespace Ashlen.Portal.Data.SqlServer
{
    /// <summary>
    /// This class encapsulates data operations for news data.
    /// </summary>
    /// <remarks>
    /// Author: Shailen Sukul
    /// Date: 08 Oct 2008
    /// </remarks>
    public class NewsDao : BaseDao, INewsDao
    {
        #region Constructor
        public NewsDao()
            : base() {
        }

        public NewsDao(string connectionString)
            : base(connectionString) {
        }
        #endregion

        #region NewsSource
        /// <summary>
        /// Loads a compete list of news providers 
        /// </summary>
        /// <returns></returns>
        public object GetNewsSources() {
            using (var t = new TransactionScope(TransactionScopeOption.Required,
                new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
            {
                RenewContext();
                return Context.NewsSource.Select(n => new { n.Id, n.Name, n.Order });
            }
        }

        /// <summary>
        /// Loads a complete list of news providers and a data container
        /// </summary>
        /// <returns></returns>
        public object GetNewsSourcesJSON() {
            using (var t = new TransactionScope(TransactionScopeOption.Required,
                   new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted })) {
                RenewContext();
                var query = from s in Context.NewsSource
                            select new { s.Id, s.Name };
                return query.ToList();
            }
        }
        
        /// <summary>
        /// Loads the news provider with all its RSS uris, based on the news provider id.
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public object GetNewsSource(int sourceId) {
            using (var t = new TransactionScope(TransactionScopeOption.Required,
                   new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted })) {
                RenewContext();
                return Context.NewsSource.Include("NewsSourceSection").FirstOrDefault(s => s.Id == sourceId);
            }
        }

        #endregion

        #region NewsSection
        /// <summary>
        /// Loads a list of news sections (category + uri) based on the provider id
        /// </summary>
        /// <param name="sourceId"></param>
        /// <returns></returns>
        public object GetNewsSection(int sourceId) {
            using (var t = new TransactionScope(TransactionScopeOption.Required,
                   new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted })) {
                RenewContext();
                return Context.NewsSection.FirstOrDefault(s => s.Id == sourceId);
            }
        }

        /// <summary>
        /// Loads a list of news sections
        /// </summary>
        /// <returns></returns>
        public object GetNewsSectionJSON() {
            using (var t = new TransactionScope(TransactionScopeOption.Required,
                   new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted })) {
                RenewContext();
                var query = from s in Context.NewsSection
                            orderby s.Order ascending
                            select new { s.Id, s.Name };
                return query.ToList();
            }
        }
        #endregion

        #region NewsFeed
        public object SearchNewsFeeds(int? sectionId, int? sourceId, int pageSize, string search, int currentPage,
            DateTime? startDate, DateTime? endDate, NewsSortBy sortBy, out int totalRecords) {
           
                totalRecords = 0;
                RenewContext();
                search = "%" + search + "%";
                ObjectParameter outputParam = new ObjectParameter("totalRecords", typeof(Int32));
                List<NewsFeed> feeds = Context.SearchNewsFeed(sectionId, sourceId, pageSize, search, currentPage, startDate, endDate,
                                    (int)sortBy, outputParam).ToList<NewsFeed>();
                string sourceName = Context.NewsSource.First(s => s.Id == sourceId).Name;
                totalRecords = Convert.ToInt32(outputParam.Value);
                return feeds.Select(f => new { f.Id, f.Caption, f.FullText, f.Uri, f.DateUpdated, f.Weighting, sourceId, sourceName });
        }

        public void HitUpdate(int id) {
            RenewContext();
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString)) {
                using (SqlCommand com = new SqlCommand()) {
                    com.Connection = con;
                    com.CommandText = string.Format("UPDATE NewsFeed SET HitCount = HitCount+1 WHERE [Id] = {0}", id);
                    if (con.State != System.Data.ConnectionState.Open)
                        com.Connection.Open();
                    com.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// Gets a list of news headlines for today only
        /// </summary>
        /// <param name="sourceId"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public object GetNewsFeeds(int sourceId, DateTime startDate, DateTime endDate, NewsSortBy sortBy) {
            using (var t = new TransactionScope(TransactionScopeOption.Required,
                new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted })) {
                RenewContext();
                var query = from f in Context.NewsFeed
                            where (f.DateUpdated > startDate && f.DateUpdated <= endDate)
                            && f.NewsSource.Id == sourceId && f.Caption != null &&
                            f.Caption.Trim() != string.Empty
                            select new { f.Id, f.Caption, f.FullText, f.Uri, f.DateUpdated, f.Weighting };
                switch (sortBy) {
                    case NewsSortBy.dateUpdated:
                        query.OrderByDescending(q => q.DateUpdated);
                        break;
                    case NewsSortBy.weight:
                        query.OrderByDescending(q => q.Weighting).OrderByDescending(q => q.DateUpdated);
                        break;
                }
                return query.ToList();
            }
        }

        /// <summary>
        /// Loads a list of news feeds based on the unique guid and news provider combination.
        /// </summary>
        /// <param name="guid"></param>
        /// <param name="sourceId"></param>
        /// <returns></returns>
        public object GetNewsFeed(string guid, int sourceId) {
            using (var t = new TransactionScope(TransactionScopeOption.Required,
                new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted })) {
                RenewContext();
                var query = from f in Context.NewsFeed
                            .Include("NewsSection").Include("NewsSource")
                            where (f.Guid == guid && f.NewsSource.Id == sourceId)
                            select f;
                return query.FirstOrDefault();
            }
        }

        public void SaveFeed(object oFeed, object oSection) {
            NewsFeed feed = oFeed as NewsFeed;
            NewsSection section = oSection as NewsSection;
            RenewContext();
            var feed2 = Context.NewsFeed.Include("NewsSection").FirstOrDefault(f => f.Id == feed.Id);
            NewsSection section2 = Context.NewsSection.FirstOrDefault(s => s.Id == section.Id);
            NewsSource source2 = Context.NewsSource.FirstOrDefault(s => s.Id == feed.NewsSource.Id);
            
            if (feed2 == null) {
                /* This is an insert */
                feed2 = new NewsFeed();
            }
            if (!feed2.NewsSection.Contains(section2)) {
                feed2.NewsSection.Add(section2);
            }
            feed2.NewsSource = source2;
            feed2.Caption = feed.Caption;
            feed2.Guid = feed.Guid;
            feed2.FullText = feed.FullText;
            feed2.Uri = feed.Uri;
            feed2.UserCreated = feed.UserCreated;
            feed2.UserUpdated = feed.UserUpdated;
            feed2.DateCreated = feed.DateCreated;
            feed2.DateUpdated = feed.DateUpdated;
            Context.SaveChanges();
        }

        /// <summary>
        /// Deletes old news feeds and keeps only the feeds for the days provided.
        /// </summary>
        /// <param name="daysToKeep"></param>
        public void DeleteOldFeeds(int daysToKeep) {
            string feedWhereClause = string.Format(@"DELETE FROM NewsFeedSection WHERE 
                                                     NewsId IN (SELECT Id FROM NewsFeed WHERE
                                                                DateUpdated < '{0}' AND DoNotDelete = 0)", DateTime.Now.AddDays((daysToKeep * -1)).
                                                                                                         ToUniversalTime().ToString("dd/MMM/yyyy"));
            feedWhereClause += string.Format("{0}DELETE FROM NewsFeed WHERE DateUpdated < '{1}' AND DoNotDelete = 0",
                Environment.NewLine,
                DateTime.Now.AddDays((daysToKeep * -1)).ToUniversalTime().ToString("dd/MMM/yyyy"));
            RenewContext();
            System.Data.Common.DbCommand com = Context.Connection.CreateCommand();
            com.CommandText = feedWhereClause;
            com.CommandType = System.Data.CommandType.Text;
            com.ExecuteNonQuery();
        }

        public object GetNewsSourceSection(int sourceId) {
            using (var t = new TransactionScope(TransactionScopeOption.Required,
                new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted })) {
                RenewContext();
                var query = from s in Context.NewsSourceSection
                            where (s.SourceId == sourceId)
                            select s;
                return query.ToList();
            }
        }
        #endregion

    }

}

